package test.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import db.util.DbcpBean;

public class DaoBean {
	private DbcpBean dbcp;
	
	
	public DaoBean() {

	}

	public DaoBean(DbcpBean dbcp) {
		this.dbcp = dbcp;
	}

	public void setDbcp(DbcpBean dbcp) {
		this.dbcp = dbcp;
	}
	public DbcpBean getDbcp() {
		return dbcp;
	}
	
	public boolean insert(DtoBean dto){
		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = "insert into jdbctest values(?,?,?)";
		
		try {
			
			con = dbcp.getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, dto.getNum());
			pstmt.setString(2, dto.getName());
			pstmt.setString(3, dto.getAddr());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException se) {
			System.out.println(se.getMessage());
			return false;
		} finally{
		
			try {
		
				if(pstmt != null){
					pstmt.close();
				}
				if(con != null){
					con.close();
				}
			} catch (SQLException se) {
				
			}
			
		}
	}
	
	public ArrayList<DtoBean> getList(){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select * from jdbctest";
		ArrayList<DtoBean> arr = new ArrayList<DtoBean>();
		try {
			
			con = dbcp.getCon();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			DtoBean dto = null;
			while(rs.next()){
				
				dto = new DtoBean(rs.getInt(1), rs.getString(2), rs.getString(3));
				
				arr.add(dto);
			}
			return arr;
			
		} catch (SQLException se) {
			System.out.println(se.getMessage());
			return null;
		} finally{
			try {
				if(rs != null){
					rs.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(con != null){
					con.close();
				}
			} catch (SQLException e) {
				
			}
		}
	}
	
	public boolean delete(String num){
		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = "delete from jdbctest where num="+num;
		
		try {
			
			con = dbcp.getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.executeUpdate();
			return true;
			
		} catch (SQLException se) {
			System.out.println(se.getMessage());
			return false;
		} finally{
			try {
				
				if(pstmt != null){
					pstmt.close();
				}
				if(con != null){
					con.close();
				}
			} catch (SQLException se) {
				
			}
		}
		
	}
	
	public boolean update(DtoBean dto){
		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = "update jdbctest set name=?, addr=? where num=?";
		
		try {
			
			con = dbcp.getCon();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setString(2, dto.getAddr());
			pstmt.setInt(3, dto.getNum());
			
			pstmt.executeUpdate();
			return true;
			
		} catch (SQLException se) {
			System.out.println(se.getMessage());
			return false;
		} finally{
			try {
				
				if(pstmt != null){
					pstmt.close();
				}
				if(con != null){
					con.close();
				}
			} catch (SQLException se) {
				
			}
		}
		
	}
	
	public DtoBean getListNum(String num){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "select * from jdbctest where num="+num;
		try {
			
			con = dbcp.getCon();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			DtoBean dto = null;
			
			if(rs.next()){
				
				dto = new DtoBean(rs.getInt(1), rs.getString(2), rs.getString(3));
				
			}
			return dto;
			
		} catch (SQLException se) {
			System.out.println(se.getMessage());
			return null;
		} finally{
			try {
				if(rs != null){
					rs.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(con != null){
					con.close();
				}
			} catch (SQLException e) {
				
			}
		}
	}
}
